var old_Repository = {
    db: null,
    queries: null,
    init: function(){
        try{
            if (typeof (window.openDatabase) != 'undefined' && window.openDatabase) {
                this.db = window.openDatabase("stb_db", '1.0', "split the bill db v1.0", 2 * 1024 * 1024);
                this.createSchema();
            }
        }catch(e){
            //do something
            //not decided yet
        }
    },
    
    createSchema: function(){
        var billTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                    'stb_bill_bil('+
                        'id_bil INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                        'date_create_bil DATE NOT NULL,' +
                        'date_for_bil DATE NOT NULL,' +
                        'name_bil TEXT NOT NULL' +  
                    ');';
        var groupTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                    'stb_group_grp('+
                        'id_grp INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                        'date_create_grp DATE NOT NULL,' +
                        'name_grp TEXT NOT NULL' +  
                    ');';
                    
        var itemTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                    'stb_item_ite('+
                        'id_ite INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                        'id_bil INTEGER,'  +
                        'name_ite,' +
                        'price_ite DECIMAL(10,2) NOT NULL' +
                    ');';
        var orderedItemTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                    'stb_ordered_item_ori('+
                        'id_ori INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                        'id_bil INTEGER NOT NULL,'  +
                        'id_per INTEGER NOT NULL,' +
                        'id_ite INTEGER NOT NULL' +
                    ');';
        var personTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                    'stb_person_per('+
                        'id_per INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                        'id_grp INTEGER,'  +
                        'name_per TEXT NOT NULL,' +
                        'email_per TEXT NOT NULL' +
                    ');';
        var particularTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                    'stb_particular_par('+
                        'id_par INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                        'id_bil INTEGER NOT NULL,'  +
                        'name_par TEXT NOT NULL,' +
                        'percent_par DECIMAL(10,2) NOT NULL,' +
                        'rule_par TEXT NOT NULL' +
                    ');';
        
        //bill table            
        this.queryDb(billTableSql, [], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
        //group table
        this.queryDb(groupTableSql, [], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
        //item table
        this.queryDb(itemTableSql, [], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
        //ordered item table
        this.queryDb(orderedItemTableSql, [], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
        //person table
        this.queryDb(personTableSql, [], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
        //particular table
        this.queryDb(particularTableSql, [], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
    },
    
    txSuccess: function(args){
        $.mobile.hidePageLoadingMsg();
    },
    
    txError: function(args){
        $.mobile.hidePageLoadingMsg();
    },
    setQueries: function(){
        this.queries = {
        };
    },
    
    queryDb: function(sql, args, success, fail){
      //tx.executeSql("SELECT COUNT(*) FROM ToDo", [], function(result){}, function(tx, error){});
        this.db.transaction(
            function(tx) {
                tx.executeSql(
                    sql,
                    args,
                    success,
                    fail
                );
            }
        );
    },
    
    /**
     * billObject = {
     *      dateCreate: 'DATE',
     *      dateFor: 'DATE',
     *      billName: 'TEXT'
     * };
     */
    //application specific queries
    addNewBill: function(billObj){
        var query = 'INSERT INTO stb_bill_bil (date_create_bil, date_for_bil, name_bil) VALUES(?,?,?);';
        this.queryDb(query, [billObj.dateCreate, billObj.dateFor, billObj.billName], $.context(this).callback(this.txSuccess), $.context(this).callback(this.txError));
    },
    
    getRecentBill: function(successFunc, errorFunc){
        var query = 'SELECT id_bil , date_create_bil, date_for_bil, name_bil FROM stb_bill_bil ORDER BY date_create_bil desc LIMIT 1;';
        this.queryDb(query, [], successFunc, errorFunc);        
    },
    
    getAllBills: function(recentBill, successFunc, errorFunc){
        var args = [];
        var query = 'SELECT id_bil, date_create_bil, date_for_bil, name_bil FROM stb_bill_bil ORDER BY date_create_bil desc;';
        if(typeof recentBill !== 'undefined' && recentBill != false && recentBill != null){
            query = 'SELECT id_bil , date_create_bil, date_for_bil, name_bil FROM stb_bill_bil WHERE id_bil !=? ORDER BY date_create_bil desc;';
            args = [recentBill.id_bil];
        }
        this.queryDb(query, args, successFunc, errorFunc);
    },
    
    getAllGroups: function(successFunc, errorFunc){
        
        //this.queryDb(query, args, successFunc, errorFunc);
    }
    
};

var STB_persist = function(){
    var that = this;
    this.db = null;
    this.init = function(){
        try{
            if (typeof (window.openDatabase) != 'undefined' && window.openDatabase) {
                this.db = window.openDatabase("stb_db", '1.0', "split the bill db v1.0", 5 * 1024 * 1024);
                this.createSchema();
            }
        }catch(e){
            //do something
            //not decided yet
        }
    };
    
    this.queryDb = function(sql, args, success, fail){
        //tx.executeSql("SELECT COUNT(*) FROM ToDo", [], function(result){}, function(tx, error){});
        this.db.transaction(
            function(tx) {
                tx.executeSql(
                    sql,
                    args,
                    success,
                    fail
                );
            }
        );
    };
    
    this.createSchema = function(){
       var queries = this.getSchemaQueries();
       //check if the tables already exist or not
       this.queryDb('SELECT name FROM sqlite_master WHERE type=? AND name like ?',
            ['table', 'stb_%'],
            function(args, res){
                if(res.rows.length != queries.length){
                   for(var i=0;i<queries.length;i++){
                       that.queryDb(queries[i], [], that.txSuccess, that.txFail);
                       
                   } 
                }
            },
            that.txFail
       );
       
       
    };
    
    this.txSuccess = function(args, result){
    };
    
    this.txFail = function(args, result){
        debugger;
    };
    
    this.describeTable = function(tableName){
        this.db.transaction(function(tx){tx.executeSql(
            'SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "'+tableName+'";', 
            [], 
            function (tx, results) {
                var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(',');
                var columnNames = [];
                for(i in columnParts) {
                    if(typeof columnParts[i] === 'string')
                    columnNames.push(columnParts[i].split(" ")[0]);
                }
                console.log(columnNames);
                
            }
        )});  
    };
    
    this.getSchemaQueries = function(){
        var billTableSql =  'CREATE TABLE IF NOT EXISTS ' + 
                            'stb_bill_bil('+
                                'id_bil INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                                'id_local_bil TEXT NOT NULL,' +
                                'name_bil TEXT NOT NULL,' +  
                                'date_create_bil DATE NOT NULL,' +
                                'create_stamp_bil TIMESTAMP DEFAULT CURRENT_TIMESTAMP' +
                            ');';  
        var itemTableSql =  'CREATE TABLE IF NOT EXISTS ' + 
                            'stb_item_ite('+
                                'id_ite INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                                'id_local_ite TEXT NOT NULL,' +
                                'id_bil INTEGER,'  +
                                'name_ite TEXT NOT NULL,' +
                                'qty_ite INTEGER,' + 
                                'price_ite DECIMAL(10,2) NOT NULL,' +
                                'date_create_ite DATE NOT NULL,' +
                                'create_stamp_ite TIMESTAMP DEFAULT CURRENT_TIMESTAMP' +
                            ');';
        var personTableSql =    'CREATE TABLE IF NOT EXISTS ' + 
                                'stb_person_per('+
                                    'id_per INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                                    'id_local_per TEXT NOT NULL,' +
                                    'id_bil INTEGER,' +
                                    'id_grp INTEGER,'  +
                                    'name_per TEXT NOT NULL,' +
                                    'id_contact_per TEXT,' +
                                    'date_create_per DATE NOT NULL,' +
                                    'create_stamp_per TIMESTAMP DEFAULT CURRENT_TIMESTAMP' +
                                ');';
        var groupTableSql = 'CREATE TABLE IF NOT EXISTS ' + 
                            'stb_group_grp('+
                                'id_grp INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                                'name_grp TEXT NOT NULL,' +  
                                'date_create_grp DATE NOT NULL,' +
                                'create_stamp_grp TIMESTAMP DEFAULT CURRENT_TIMESTAMP' +
                            ');';
        var orderedItemTableSql =   'CREATE TABLE IF NOT EXISTS ' + 
                                    'stb_ordered_item_ori('+
                                        'id_ori INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'  +
                                        'id_bil INTEGER NOT NULL,'  +
                                        'id_per INTEGER NOT NULL,' +
                                        'id_ite INTEGER NOT NULL' +
                                    ');';
        var schemaArr = new Array;
        schemaArr.push(billTableSql);
        schemaArr.push(itemTableSql);
        schemaArr.push(personTableSql);
        schemaArr.push(groupTableSql);
        schemaArr.push(orderedItemTableSql);
        return schemaArr;
    };
    
    this.init();
}





